﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Text;

/// <summary>
/// Summary description for DB
/// </summary>
public class DB
{
    private static readonly string sqlConstring = "Data Source=LONGRAY;Initial Catalog=AJAX;User ID=sa;Password=saadmin";
    public DB()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    /// <summary>
    ///获取友情链接信息
    /// </summary>
    /// <param name="pageIndex"></param>
    /// <param name="pageSize"></param>
    /// <returns></returns>
    public static DataTable GetFriendLinkList(int pageIndex, int pageSize)
    {
        string strSql = string.Format("SELECT TOP {0} linkID,linkSort,linkName,linkUrl,linkDisplay,linkDesc,CONVERT(VARCHAR(10),linkAddDate,126) linkAddDate FROM tb_friendlink WHERE linkID NOT IN (SELECT TOP {1} linkID FROM tb_friendlink ORDER BY linkID DESC) ORDER BY linkID DESC", pageSize, pageSize * (pageIndex - 1));
        using (SqlConnection conn = new SqlConnection(sqlConstring))
        {
            conn.Open();
            SqlDataAdapter sda = new SqlDataAdapter(strSql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds.Tables[0];
        }
    }

    public static string GetRecord()
    {
        string strSql = "SELECT COUNT(1) FROM tb_friendlink";
        using (SqlConnection conn = new SqlConnection(sqlConstring))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql, conn);
            return cmd.ExecuteScalar().ToString();
        }
    }

    public static FriendLinkEntity GetModel(int linkID)
    {
        string strSql = string.Format("SELECT * FROM tb_friendlink WHERE linkID={0}", linkID);
        using (SqlConnection conn = new SqlConnection(sqlConstring))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql, conn);
            FriendLinkEntity model = new FriendLinkEntity();
            SqlDataReader sdr = cmd.ExecuteReader();
            while (sdr.Read())
            {
                model.linkID = int.Parse(sdr["linkID"].ToString());
                model.linkName = sdr["linkName"].ToString();
                model.linkSort = int.Parse(sdr["linkSort"].ToString());
                model.linkDisplay = bool.Parse(sdr["linkDisplay"].ToString());
                model.linkDesc = sdr["linkDesc"].ToString();
                model.linkAddDate = Convert.ToDateTime(sdr["linkAddDate"]);
                model.linkUrl = sdr["linkUrl"].ToString();
            }
            sdr.Close();
            return model;
        }
    }

    /// <summary>
    /// 添加一条信息
    /// </summary>
    /// <param name="model"></param>
    /// <returns></returns>
    public static bool Add(FriendLinkEntity model)
    {
        StringBuilder sb = new StringBuilder("INSERT INTO tb_friendlink(");
        sb.Append("linkSort,linkName,linkDisplay,linkUrl,linkDesc,linkAddDate)");
        sb.Append("VALUES(");
        sb.AppendFormat("{0},", model.linkSort);
        sb.AppendFormat("'{0}',", model.linkName);
        sb.AppendFormat("'{0}',", model.linkDisplay);
        sb.AppendFormat("'{0}',", model.linkUrl);
        sb.AppendFormat("'{0}',", model.linkDesc);
        sb.AppendFormat("'{0}')", model.linkAddDate);
        using (SqlConnection conn = new SqlConnection(sqlConstring))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
            return cmd.ExecuteNonQuery() == 1;
        }
    }

    /// <summary>
    /// 修改一条信息
    /// </summary>
    /// <param name="model"></param>
    /// <returns></returns>
    public static bool Update(FriendLinkEntity model)
    {
        StringBuilder sb = new StringBuilder("UPDATE tb_friendlink SET ");
        sb.AppendFormat("linkSort={0},", model.linkSort);
        sb.AppendFormat("linkName='{0}',", model.linkName);
        sb.AppendFormat("linkDisplay='{0}',", model.linkDisplay);
        sb.AppendFormat("linkDesc='{0}',", model.linkDesc);
        sb.AppendFormat("linkUrl='{0}'", model.linkUrl);
        sb.AppendFormat(" WHERE linkID={0}", model.linkID);
        using (SqlConnection conn = new SqlConnection(sqlConstring))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
            return cmd.ExecuteNonQuery() == 1;
        }
    }

    /// <summary>
    /// 删除一条记录
    /// </summary>
    /// <param name="linkID"></param>
    /// <returns></returns>
    public static bool Delete(int linkID)
    {
        string strSql = string.Format("DELETE FROM tb_friendlink WHERE linkID={0}", linkID);
        using (SqlConnection conn = new SqlConnection(sqlConstring))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql,conn);
            return cmd.ExecuteNonQuery() == 1;
        }
    }

    /// <summary>
    /// 删除多个链接
    /// </summary>
    /// <param name="list"></param>
    /// <returns></returns>
    public static bool Delete(IList<string> list)
    {
        StringBuilder sb = new StringBuilder();
        foreach (string id in list)
        {
            sb.AppendFormat("{0},", id);
        }
        string strSql = "DELETE FROM tb_friendlink WHERE linkID IN(" + sb.ToString().Substring(0, sb.ToString().LastIndexOf(',')) + ")";
        using (SqlConnection conn = new SqlConnection(sqlConstring))
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand(strSql, conn);
            return cmd.ExecuteNonQuery() == list.Count;
        }
    }
}
